package Dao;

import Bean.BeanTeacher;
import Bean.BeanUser;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class TeacherDao {
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    public void teacheradd(String teacherid, String pwd,String teachername,String teacher_photo,String teacher_introduction) throws Exception {
        String sql="select user_id from user where user_id=?";
        BeanUser user=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanUser>() {
            @Override
            public BeanUser extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                if(rs.next()) {
                    BeanUser user=new BeanUser();
                    user.setUser_id(rs.getString(1));
                    return user;
                }
                else
                    return null;
            }},teacherid);
        if(user==null) {
            sql="insert into user(user_id,pwd,user_name,user_type,user_status) values(?,?,?,2,1)";
            this.jdbcTemplate.update(sql,new Object[] {teacherid,pwd,teachername});
            sql="insert into teacher(teacher_id,teacher_name,teacher_photo,teacher_introduction) values(?,?,?,?)";
            this.jdbcTemplate.update(sql,new Object[] {teacherid,teachername,teacher_photo,teacher_introduction});
        }
        else throw new Exception("教师id已被占用");
    }
    public void teacherupdate(String teacherid,String teachername,String pwd,String teacher_photo,String teacher_introduction) throws Exception {
        String sql="update teacher set teacher_name=?,teacher_photo=?,teacher_introduction=? where teacher_id=?";
        this.jdbcTemplate.update(sql,new Object[] {teachername,teacher_photo,teacher_introduction,teacherid});
        sql="update user set user_name=?,pwd=? where user_id=?";
        this.jdbcTemplate.update(sql,new Object[] {teachername,pwd,teacherid});
    }
    public void teacherdelete(String teacherid) throws Exception{
        String sql="delete from teacher where teacher_id=?";
        this.jdbcTemplate.update(sql,new Object[] {teacherid});
        sql="delete from user where user_id=?";
        this.jdbcTemplate.update(sql,new Object[] {teacherid});
    }
    public ArrayList<BeanTeacher> getTeachers() throws Exception {
        String sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher a left outer join user b on a.teacher_id=b.user_id where b.user_status=1";
        ArrayList<BeanTeacher> teachers=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
            @Override
            public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanTeacher teacher=new BeanTeacher();
                    teacher.setTeacher_id(rs.getString(1));
                    teacher.setTeacher_name(rs.getString(2));
                    teacher.setTeacher_photo(rs.getString(3));
                    teacher.setTeacher_introduction(rs.getString(4));
                    teachers.add(teacher);
                }
                return null;
            }});
        for(BeanTeacher teacher:teachers){
            sql="select user_id,user_name,pwd from user where user_id=?";
            BeanUser user=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanUser>() {
                @Override
                public BeanUser extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanUser user=new BeanUser();
                        user.setUser_id(rs.getString(1));
                        user.setUser_name(rs.getString(2));
                        user.setPwd(rs.getString(3));
                        user.setUser_type(2);
                        return user;
                    }
                    return null;
                }},teacher.getTeacher_id());
            teacher.setUser(user);
        }
        return teachers;
    }
    public ArrayList<BeanTeacher> getTeachersByKeyword(String keyword) throws Exception {
        String sql="select teacher_id,teacher_name,teacher_photo,teacher_introduction from teacher a left outer join user b on a.teacher_id=b.user_id where b.user_status=1 and (teacher_id like '%"+keyword+"%' or teacher_name like '%"+keyword+"%')";
        ArrayList<BeanTeacher> teachers=new ArrayList<>();
        this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanTeacher>() {
            @Override
            public BeanTeacher extractData(ResultSet rs) throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                while(rs.next()) {
                    BeanTeacher teacher=new BeanTeacher();
                    teacher.setTeacher_id(rs.getString(1));
                    teacher.setTeacher_name(rs.getString(2));
                    teacher.setTeacher_photo(rs.getString(3));
                    teacher.setTeacher_introduction(rs.getString(4));
                    teachers.add(teacher);
                }
                return null;
            }});
        for(BeanTeacher teacher:teachers){
            sql="select user_id,user_name,pwd from user where user_id=?";
            BeanUser user=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanUser>() {
                @Override
                public BeanUser extractData(ResultSet rs) throws SQLException, DataAccessException {
                    // TODO Auto-generated method stub
                    if(rs.next()) {
                        BeanUser user=new BeanUser();
                        user.setUser_id(rs.getString(1));
                        user.setUser_name(rs.getString(2));
                        user.setPwd(rs.getString(3));
                        user.setUser_type(2);
                        return user;
                    }
                    return null;
                }},teacher.getTeacher_id());
            teacher.setUser(user);
        }
        return teachers;
    }
}
